#-*- coding:utf-8 -*-

import thread,sys,time
import MySQLdb
import hashlib
from datetime import date,timedelta,datetime
from free_db import free_list,free_symb_list
from base_crul import get_all_opurls
#reload(sys) 
#sys.setdefaultencoding('utf8')

db_name='mpsupport'
db_user='ccsdev'
db_passwd = "czacc603+"


today=date.today()#当天
todaystr=today.strftime('%Y-%m-%d')
lastday=date.today()+timedelta(days=-1)#当前日期的前一天
lastdaystr=lastday.strftime('%Y-%m-%d')

conn = MySQLdb.connect(host="localhost",
                       port=3306,
                       user=db_user,
                       passwd=db_passwd,
                       db=db_name,
                       charset="utf8")

period_dict={'w':-7,
             'm':-30,
             '3m':-60,
             '6m':-180,
             'y':-365,
             }

#db = web.database(dbn='mysql', db='blog', user='justin')
### 执行sql命令，创建table test
global lock
lock = thread.allocate_lock()

def sql_excute(sql):
    lock.acquire() #
    conn.ping(True)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    result=cursor.fetchall()
    lock.release()
    
    return result


def dry_run_sql(sql):
    lock.acquire() #
    conn.ping(True)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.commit()
    lock.release()
    

def init_symb_list(symb_dict_list,type):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #判断标题，如存在则不插入
    sql = "insert ignore into tbl_symb_list (symb,cname,type) values (%s,%s,%s)"
    for stock in symb_dict_list:
        param = (stock['symb'],#1
                 stock['name'],#2,
                 type,
                 )
        lock.acquire() 
        cursor.execute(sql,param)
        conn.commit()
        lock.release() 

def init_symb_expdate_list(symb_list):
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #判断标题，如存在则不插入,status默认为1
    sql = "insert ignore into tbl_symb_expdate_list (symb,expdate,status,sy_exp,opurl) values (%s,%s,1,%s,%s)"
    for symb in symb_list:
        print symb
        time.sleep(3)
        all_opurls=get_all_opurls(symb)
        #expdate_list=get_expdate_list(symb)       
        for k,v in all_opurls.items():
            param=(symb,
                   k,
                   symb+k,
                   v,
                   )
            lock.acquire()
            cursor.execute(sql,param)
            conn.commit()
            lock.release() 
            

#update:
#1. if expdate>currentDate, get the symb and set status to 0.
#2. update expdate table by symb and set status to default 1

#如果是前一天过期的则置状态为0           
def update_expdate_list_status(cday=lastdaystr): 
    sql="update tbl_symb_expdate_list SET status=0 where expdate='%s'"%cday
    dry_run_sql(sql)

#找出需要计算mp的opurl地址，即状态为1    
def get_daily_check_mp_need(cday=todaystr):
    sql="select sy_exp,opurl from tbl_symb_expdate_list where status=1"
    check_need=sql_excute(sql)
    
    return check_need

def update_daily_mp2(sy_exp,mp):
    #sql="INSERT INTO tbl_daily_mp (sy_exp,mp,date) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE mp='%s'"%(sy_exp,mp,todaystr,mp)
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    #sql = "insert ignore into tbl_daily_mp (sy_exp,mp,date) values (%s,%s,%s)"%(sy_exp,mp,todaystr)
    sql = "insert ignore into tbl_daily_mp (sy_exp,mp,date) values (%s,%s,%s)"
    #print sql
    param=(sy_exp,
           mp,
           todaystr,
           )
    lock.acquire()
    cursor.execute(sql,param)
    conn.commit()
    lock.release() 

def update_daily_mp(mp_dict):
    #sql="INSERT INTO tbl_daily_mp (sy_exp,mp,date) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE mp='%s'"%(sy_exp,mp,todaystr,mp)
    conn.ping(True)#保持数据库连接,减少mysql gone away错误
    cursor = conn.cursor()
    conn.select_db(db_name)
    
    sql="INSERT INTO tbl_daily_mp (sy_exp,mp,date,mp_hash) VALUES (%s,%s,%s,%s) ON DUPLICATE KEY UPDATE mp='%s'"
    #sql = "insert ignore into tbl_daily_mp (sy_exp,mp,date) values (%s,%s,%s)"%(sy_exp,mp,todaystr)
    #sql = "insert ignore into tbl_daily_mp (sy_exp,mp,date,mp_hash) values (%s,%s,%s,%s)"
    #print sql
    for k,v in mp_dict.items():
        mp_hash=hashlib.new("md5", k+todaystr).hexdigest() 
        param=(k,
               v,
               todaystr,
               mp_hash,
               v,
               )
        lock.acquire()
        cursor.execute(sql,param)
        conn.commit()
        lock.release() 

def get_mp_list(sy_exp,tag):
    #sql="select mp from tbl_daily_mp where sy_exp='%s' and date>=curdate()"
    
    before_days=period_dict[tag]
    #print before_days
    vday=date.today()+timedelta(days=before_days)#当前日期的前N天
    #print vday
    vdaystr=vday.strftime('%Y-%m-%d')
    
    sql="select date,mp from tbl_daily_mp where sy_exp='%s' and date>='%s'"%(sy_exp,vdaystr)
    
    mp_list=sql_excute(sql)
    
    return mp_list

#########################for init action##
def init_n100_symb_list():
    from free_db import get_n100_fromtxt
    n100_list=get_n100_fromtxt()
    init_symb_list(n100_list,'nasdaq100') 
   
def init_n100_symb_expdate_list():
    from free_db import get_n100_fromtxt,get_symb_list
    n100_list=get_n100_fromtxt()
    n100_symb_list=get_symb_list(n100_list)
    init_symb_expdate_list(n100_symb_list)

def init_free_symb_list():
    init_symb_list(free_list,'free')

#######################end of init actions##

def get_symblist_by_type(symb_type):
    sql="select symb,cname from tbl_symb_list where type='%s'" %symb_type
    symblists=sql_excute(sql)
    
    return symblists
     
if __name__ == "__main__":
    
    #init_free_symb_list()
    #init_symb_expdate_list()
    #init_n100_symb_list()
    #init_n100_symb_expdate_list()
    pass